package zd

import (
	"bytes"
	"context"
	"fmt"
	"html/template"
	"reflect"
	"slices"
	"strconv"
	"strings"

	"gitee.com/chunanyong/zorm"
	"github.com/pkg/errors"
)

func schemaName(ctx context.Context) string {
	return ctx.Value(Schema).(string)
}

type IEntity interface {
	zorm.IEntityStruct
	GetPrimaryKey() []string // 主键
	GetTableNamePre() string // 表名前置字串
}

// Entity 实现 IEntity
type Entity struct{}

// GetTableName 返回struct类型名;需要struct自行实现
func (Entity) GetTableName() string { return "" }

// GetTableNamePre 表名前置字串
func (Entity) GetTableNamePre() string { return "" }

// GetPKColumnName 主键列名
func (Entity) GetPKColumnName() string { return "" }

// GetPkSequence 获取主键
func (Entity) GetPkSequence() string { return "" }

// GetPrimaryKey 表的主键, 返回 nil 时忽略此函数; 否则以此函数为准, 忽略 tag中的 primaryKey 配置
func (Entity) GetPrimaryKey() []string { return nil }

// GetDefaultValue 获取列的默认值Map,用于Insert和Update Struct对象,UpdateNotZeroValue请使用BindContextMustUpdate方法.返回map的key是Struct属性名,value是默认值,value可以是nil.
func (Entity) GetDefaultValue() map[string]any { return nil }

// GetTableName 取表名
//
//	@param ctx
//	@return string SchemaName.TblName(DM:Camel PG:snake)
func GetTableName[T IEntity](ctx context.Context, entities ...T) string {
	var entity T
	if len(entities) == 0 {
		entity = *new(T)
	} else {
		entity = entities[0]
	}
	tblName := entity.GetTableName()
	if tblName != "" {
		if !strings.Contains(tblName, ".") {
			tblName = schemaName(ctx) + "." + entity.GetTableNamePre() + tblName
		}
	} else {
		sType := reflect.TypeOf(entity)
		if sType.Kind() == reflect.Ptr {
			// 传入的inStructPtr是指针，需要.Elem()取得指针指向的value
			sType = sType.Elem()
		}
		tblName = entity.GetTableNamePre() + sType.Name()
		if ctx.Value(PG) != nil {
			tblName = toSnakeString(tblName)
		} else { // DM oracle
			tblName = strings.ToUpper(tblName)
		}
		if len(schemaName(ctx)) > 0 {
			tblName = schemaName(ctx) + "." + tblName
		}
	}
	return tblName
}

// TableColumn 数据列定义
type TableColumn struct {
	ColumnName      string // 数据表字段名(字段名or由columns定义)
	StructFieldName string // 结构体字段名称
	ColumnType      string // 数据表中的类型定义
	Comment         string
	PrimaryKey      bool
	NotNull         bool
	Unique          bool
	Index           bool
	AutoIncrement   bool // 自增
	Default         string
}

// GetColumnNameFromDB 取列名(从dm库中取)
//
//	@param ctx
//	@param tblName 表名,格式: SchemaName.TblName
//	@return []string 列名表
func GetColumnNameFromDB(ctx context.Context, tblName string) []string {
	var keys = make([]string, 0)
	var sqlStr string
	if ctx.Value(PG) != nil { // postgresql
		sqlStr = "select column_name from information_schema.columns where table_schema=? and table_name=? order by ordinal_position "
	} else {
		sqlStr = "SELECT COLUMN_NAME FROM all_tab_columns WHERE owner = ? AND table_name = ?"
	}
	err := zorm.Query(ctx, zorm.NewFinder().Append(sqlStr, strings.Split(tblName, ".")[0], strings.Split(tblName, ".")[1]), &keys, nil)
	if err != nil {
		fmt.Print(err)
	}
	return keys
}

// GetPrimaryKey 取表的主键
//
//	@return []string 主键(已适配数据库)
func GetPrimaryKey[T IEntity](ctx context.Context, entities ...T) []string {
	var entity T
	if len(entities) == 0 {
		entity = *new(T)
	} else {
		entity = entities[0]
	}
	// 用表的函数实现覆盖 tag
	if keys := entity.GetPrimaryKey(); keys != nil {
		return keys
	}
	keys := make([]string, 0)
	for _, v := range GetColumns(ctx, entity) {
		if v.PrimaryKey {
			keys = append(keys, v.ColumnName)
		}
	}
	return keys
}

// GetColumns 取列定义
//
//	@param ctx
//	@param entity 指针
//	@return []TableColumn 数据列定义
func GetColumns[T IEntity](ctx context.Context, entities ...T) []TableColumn {
	var entity T
	if len(entities) == 0 {
		entity = *new(T)
	} else {
		entity = entities[0]
	}
	cols := getColumns(ctx, entity)
	return cols
}

// getColumns 取列定义(已经处理 PG 为 snake)(相同名称,后声明覆盖前声明)
//
//	@param ctx
//	@param t
//	@return []TableColumn
func getColumns(ctx context.Context, t any) []TableColumn {
	//TypeOf会返回目标数据的类型，比如int/float/struct/指针等
	sType := reflect.TypeOf(t)
	sVal := reflect.ValueOf(t)
	if sType.Kind() == reflect.Ptr {
		// 传入的inStructPtr是指针，需要.Elem()取得指针指向的value
		sType = sType.Elem()
		sVal = sVal.Elem()
	}
	// 获取结构体字段
	columns := make([]TableColumn, 0)
	// 遍历结构体字段
	subStructFieldNum := make([]int, 0)
	for i := 0; i < sType.NumField(); i++ {
		// 判断字段是否为结构体类型，或者是否为指向结构体的指针类型
		if sVal.Field(i).Kind() == reflect.Struct { // 组合 struct
			if reflect.TypeOf(sVal.Field(i).Interface()).Name() != "Time" {
				subStructFieldNum = append(subStructFieldNum, i)
				continue
			}
		}
		typ := sType.Field(i) // 字段的数据类型
		column := TableColumn{
			ColumnName: typ.Name,
		}
		// 处理标签约定
		tag := typ.Tag
		tags := make(map[string]string) // 标签名均转换为小写
		if tagStr := tag.Get("zorm"); tagStr != "" {
			for _, v := range strings.Split(tagStr, ";") {
				kv := strings.SplitN(v, ":", 2)
				if len(kv) == 2 {
					tags[strings.ToLower(kv[0])] = kv[1]
				} else {
					tags[strings.ToLower(kv[0])] = ""
				}
			}
		}
		if ctx != nil && ctx.Value(PG) != nil { // 处理 pg
			switch typ.Type.Kind() { // 类型名用小写
			case reflect.Ptr, reflect.UnsafePointer, reflect.Uintptr:
				continue
			case reflect.Struct: // 时间类型（其他struct会被取子字段)
				column.ColumnType = "timestamp"
			case reflect.Int, reflect.Int32, reflect.Int64:
				column.ColumnType = "int"
			case reflect.Uint8: // 自定义 rune
				if typ.Type.Name() != "uint8" {
					column.ColumnType = "char(1)"
				} else {
					column.ColumnType = "uint8"
				}
			case reflect.String:
				if size, ok := tags["size"]; ok {
					column.ColumnType = "varchar(" + size + ")"
				} else {
					column.ColumnType = "varchar(256)"
				}
			case reflect.Float64:
				column.ColumnType = "float8"
			case reflect.Float32:
				column.ColumnType = "float4"
			default:
				column.ColumnType = typ.Type.Kind().String()
			}
		} else { // 类型名均用大写
			switch typ.Type.Kind() {
			case reflect.Ptr, reflect.UnsafePointer, reflect.Uintptr:
				continue
			case reflect.Struct: // 时间类型（其他struct会被取子字段)
				column.ColumnType = "TIMESTAMP"
			case reflect.Int:
				column.ColumnType = "INTEGER"
			case reflect.String:
				if size, ok := tags["size"]; ok {
					column.ColumnType = "VARCHAR(" + size + ")"
				} else {
					column.ColumnType = "VARCHAR(256)"
				}
			case reflect.Float64, reflect.Float32:
				column.ColumnType = "NUMBER(22, 6)"
			case reflect.Bool:
				if ctx.Value(DM) != nil {
					column.ColumnType = "BIT"
				} else {
					column.ColumnType = "CHAR(1)" // oracle 布尔类型
				}
			case reflect.Uint8: // 自定义 rune
				if typ.Type.Name() != "uint8" {
					column.ColumnType = "CHAR(1)"
				} else {
					column.ColumnType = "INTEGER"
				}
			case reflect.Int32: // 自定义 rune
				if typ.Type.Name() != "int32" {
					column.ColumnType = "CHAR(1)"
				} else {
					column.ColumnType = "INTEGER"
				}
			default:
				fmt.Print("不支持的类型:", column.ColumnName, typ.Type.Align(), typ.Type.Kind())
				continue
			}
		}
		// ColumnName
		if columnName, ok := tags["column"]; ok {
			column.ColumnName = columnName
		} else {
			if ctx.Value(PG) != nil { // postgresql
				column.ColumnName = toSnakeString(column.ColumnName)
			} else {
				column.ColumnName = strings.ToUpper(column.ColumnName) // 列名大写
			}
		}
		// ColumnType
		if typeName, ok := tags["type"]; ok {
			column.ColumnType = typeName
		}
		// GetDataType 函数自定义类型
		v := sVal.Field(i)
		if m := v.MethodByName("GetDataType"); m.IsValid() { // 零值的IsValid方法返回false，其Kind方法返回Invalid，其String方法返回""，对零值的任何其他方法的调用均会导致panic
			if dateType := m.Call(nil); len(dateType) > 0 {
				column.ColumnType = dateType[0].String()
			}
		}
		// Default 默认值
		if m := v.MethodByName("GetDefault"); m.IsValid() {
			if def := m.Call(nil); len(def) > 0 {
				column.Default = def[0].String()
			}
		}
		if def, ok := tags["default"]; ok {
			if b, err := strconv.ParseBool(def); err == nil && ctx.Value(PG) == nil { // bool 型(非pg)
				if b {
					column.Default = "1"
				} else {
					column.Default = "0"
				}
			} else {
				column.Default = def
			}
		}
		_, column.PrimaryKey = tags["primarykey"]
		_, column.AutoIncrement = tags["autoincrement"] // 所有 tag 全小写
		_, column.Unique = tags["unique"]
		_, column.Index = tags["index"]
		_, column.NotNull = tags["not null"]
		column.Comment = tags["comment"]
		column.StructFieldName = typ.Name
		columns = append(columns, column)
	}
	// **** mp 处理过程中不能打乱列的顺序 ****
	// 处理相同字段名时, 后声明覆盖前声明
	if len(subStructFieldNum) > 0 {
		mpColumns := make(map[string]TableColumn)
		for _, col := range columns {
			mpColumns[col.ColumnName] = col
		}
		for _, i := range subStructFieldNum {
			for _, col := range getColumns(ctx, sVal.Field(i).Interface()) {
				if _, exists := mpColumns[col.ColumnName]; exists { // 存在则替换
					idx := slices.IndexFunc(columns, func(item TableColumn) bool { return item.ColumnName == col.ColumnName })
					columns = slices.Replace(columns, idx, idx+1, col)
				} else {
					columns = append(columns, col)
					mpColumns[col.ColumnName] = col
				}
			}
		}
	}
	return columns
}

// CreateSchema creates a new schema
//
//	@param ctx
//	@return error
func CreateSchema(ctx context.Context) error {
	if ctx.Value(PG) != nil {
		return execSql(ctx, fmt.Sprintf(`CREATE SCHEMA if not exists %s AUTHORIZATION postgres;`, schemaName(ctx)))
	}
	return execSql(ctx, fmt.Sprintf(`CREATE SCHEMA %s AUTHORIZATION SYSDBA;`, schemaName(ctx)))
}

// DropTable drops a table
//
//	@param ctx
//	@return error
func DropTable[T IEntity](ctx context.Context, entities ...T) error {
	var entity T
	if len(entities) == 0 {
		entity = *new(T)
	} else {
		entity = entities[0]
	}
	// defer dbDao.CloseDB()
	return execSql(ctx, fmt.Sprintf(`drop table if exists %s`, GetTableName(ctx, entity)))
}

// TrunTable truncates a table
//
//	@param ctx
//	@return error
func TrunTable[T IEntity](ctx context.Context, entities ...T) error {
	var entity T
	if len(entities) == 0 {
		entity = *new(T)
	} else {
		entity = entities[0]
	}
	// defer dbDao.CloseDB()
	return execSql(ctx, fmt.Sprintf(`truncate table %s`, GetTableName(ctx, entity)))
}

// CreateTable creates a new table
//
// 主键primaryKey;列名column:User_Name;长度size:12;非空not null;索引index;默认default
//
//	@param ctx
//	@return err
func CreateTable[T IEntity](ctx context.Context, entities ...T) (err error) {
	var entity T
	if len(entities) == 0 {
		entity = *new(T)
	} else {
		entity = entities[0]
	}
	// defer dbDao.CloseDB()
	tblName := GetTableName(ctx, entity)
	var mps []map[string]any
	if ctx.Value(PG) != nil {
		mps, err = QueryMap(ctx, fmt.Sprintf("SELECT COUNT(1) FROM information_schema.tables WHERE table_schema='%s' and table_type='BASE TABLE' AND table_name='%s'", strings.Split(tblName, ".")[0], strings.Split(tblName, ".")[1]))
	} else {
		if ctx.Value(ORACLE) != nil { // oracle
			mps, err = QueryMap(ctx, fmt.Sprintf("SELECT COUNT(1) FROM ALL_TABLES WHERE owner='%s' AND table_name='%s'", strings.Split(tblName, ".")[0], strings.Split(tblName, ".")[1]))
		} else { // 达梦
			mps, err = QueryMap(ctx, fmt.Sprintf("SELECT COUNT(1) FROM dba_segments WHERE owner='%s' AND segment_name='%s'", strings.Split(tblName, ".")[0], strings.Split(tblName, ".")[1]))
		}
	}
	if err != nil {
		return err
	}
	for _, v := range mps[0] {
		if n, _ := strconv.Atoi(fmt.Sprintf("%v", v)); n > 0 {
			return nil
		}
	}
	var tmplStr string
	if ctx.Value(PG) != nil {
		tmplStr = fmt.Sprintf(`CREATE TABLE  if not exists %s ({{range $i,$v := .}} {{if gt $i 0}}, {{end}} {{.ColumnName}} {{if .AutoIncrement}}serial{{else}}{{.ColumnType}}{{end}} {{end}}) ;`, tblName)
	} else {
		tmplStr = fmt.Sprintf(`CREATE TABLE  %s ({{range $i,$v := .}}{{if gt $i 0}}, {{end}}{{.ColumnName}} {{.ColumnType}} {{end}})`, tblName)
	}
	tmplCreate, _ := template.New("create").Parse(tmplStr)
	columns := GetColumns(ctx, entity)
	var buf = bytes.Buffer{}
	tmplCreate.Execute(&buf, columns)

	// create table 用 ?.? 报错, 可能与 string 加 ' 有关
	err = execSql(ctx, buf.String())
	if err != nil {
		return errors.Wrap(err, "create table")
	}

	var pre string // idx 前缀
	if ctx.Value(PG) != nil {
		pre = strings.Split(tblName, ".")[1] // 不包括 schema
	} else {
		pre = tblName
	}

	primarys := make([]string, 0)
	for _, v := range columns {
		// not null 非空
		if v.NotNull {
			err = execSql(ctx, fmt.Sprintf(`alter table %s alter column %s set not null;`, tblName, v.ColumnName))
			if err != nil {
				return errors.Wrap(err, "alter not null")
			}
		}
		if v.PrimaryKey {
			primarys = append(primarys, v.ColumnName)
		}
		// default 默认值
		if v.Default != "" {
			// execSql(ctx, fmt.Sprintf(`alter table %s alter column %s set not null;`, tblName, v.ColumnName))
			err = execSql(ctx, fmt.Sprintf(`alter table %s alter column %s set default %s;`, tblName, v.ColumnName, v.Default))
			if err != nil {
				return errors.Wrap(err, "alter default")
			}
		}
		// 自增
		if v.AutoIncrement {
			if ctx.Value(PG) != nil { // pg
			} else if ctx.Value(DM) != nil {
				execSql(ctx, fmt.Sprintf(`alter table %s alter column %s set not null;`, tblName, v.ColumnName))
				err = execSql(ctx, fmt.Sprintf(`alter table %s add column %s identity(1, 1);`, tblName, v.ColumnName))
				if err != nil {
					return errors.Wrap(err, "alter AutoIncrement")
				}
			} else if ctx.Value(ORACLE) != nil {
				return errors.New("Oracle 不支持 自增ID")
			}
		}
		// unique 唯一索引
		if v.Unique {
			err = execSql(ctx, fmt.Sprintf(`create unique index if not exists %s_%s on %s(%s);`, pre, v.ColumnName, tblName, v.ColumnName))
			if err != nil {
				return errors.Wrap(err, "create unique index")
			}
		} else { // index 索引
			if v.Index {
				err = execSql(ctx, fmt.Sprintf(`create index if not exists %s_%s on %s(%s);`, pre, v.ColumnName, tblName, v.ColumnName))
				if err != nil {
					return errors.Wrap(err, "create index")
				}
			}
		}
		// comment 注释
		if v.Comment != "" {
			err = execSql(ctx, fmt.Sprintf(`comment on column %s.%s is '%s';`, tblName, v.ColumnName, v.Comment))
			if err != nil {
				return errors.Wrap(err, "comment on column")
			}
		}
		// check 阀值检查(例 direction 只能是 0买 1卖, 根据声明对应的 enum 自动生成)
	}
	// primaryKey 主键; 可用函数实现覆盖
	if keys := GetPrimaryKey(ctx, entity); keys != nil {
		primarys = keys
	}
	if len(primarys) > 0 {
		err = execSql(ctx, fmt.Sprintf(`alter table %s add primary key(%s);`, tblName, strings.Join(primarys, ",")))
		if err != nil {
			return errors.Wrap(err, "alter primarykey")
		}
	}
	return nil
}

// execSql
//
//	@param ctx
//	@param sql
//	@return error
func execSql(ctx context.Context, sql string) error {
	_, err := zorm.Transaction(ctx, func(ctx context.Context) (any, error) {
		var finder = zorm.NewFinder()
		finder.InjectionCheck = false
		finder.Append(sql)
		finder.InjectionCheck = false
		finder.GetSQL()
		return zorm.UpdateFinder(ctx, finder)
	})
	if err != nil {
		return errors.Wrap(err, "执行 SQL")
	}
	return nil

}
